#!/bin/bash
# 词典单词自动导入脚本
# 功能：自动将 /root/workspace/3.1.0-dev/work/aiword/public/dicts/ 目录下的JSON文件导入到数据库

set -e

# 配置
DB_CONTAINER="ruoyi-mysql"
DB_USER="root"
DB_PASS="123"
DB_NAME="ry-vue"
DICT_DIR="/root/workspace/3.1.0-dev/work/aiword/public/dicts"

echo "=========================================="
echo "词典单词自动导入工具"
echo "=========================================="
echo ""

# 检查jq是否安装（用于解析JSON）
if ! command -v jq &> /dev/null; then
    echo "安装 jq 工具（JSON解析）..."
    yum install -y jq > /dev/null 2>&1 || apt-get install -y jq > /dev/null 2>&1
fi

# 获取所有词典列表
echo "[1/3] 从数据库获取词典列表..."
BOOKS=$(docker exec -i $DB_CONTAINER mysql -u$DB_USER -p$DB_PASS \
    --default-character-set=utf8mb4 $DB_NAME -N -B \
    -e "SELECT id, name, url FROM dict_book WHERE url IS NOT NULL AND url != '';" 2>/dev/null)

if [ -z "$BOOKS" ]; then
    echo "❌ 没有找到词典记录"
    exit 1
fi

BOOK_COUNT=$(echo "$BOOKS" | wc -l)
echo "✓ 找到 $BOOK_COUNT 个词典"
echo ""

# 导入每个词典
echo "[2/3] 开始导入单词..."
SUCCESS=0
FAILED=0
SKIPPED=0

while IFS=$'\t' read -r BOOK_ID BOOK_NAME URL; do
    # 提取文件名
    FILENAME=$(basename "$URL")
    JSON_FILE="${DICT_DIR}/${FILENAME}"
    
    # 检查文件是否存在
    if [ ! -f "$JSON_FILE" ]; then
        echo "⚠️  跳过：找不到文件 $FILENAME ($BOOK_NAME)"
        ((FAILED++))
        continue
    fi
    
    # 检查是否已导入
    WORD_COUNT=$(docker exec -i $DB_CONTAINER mysql -u$DB_USER -p$DB_PASS \
        --default-character-set=utf8mb4 $DB_NAME -N -B \
        -e "SELECT COUNT(*) FROM dict_word WHERE book_id='$BOOK_ID';" 2>/dev/null)
    
    if [ "$WORD_COUNT" -gt 0 ]; then
        echo "⏭️  跳过：已导入 $BOOK_NAME ($WORD_COUNT 个单词)"
        ((SKIPPED++))
        continue
    fi
    
    echo "📚 导入：$BOOK_NAME"
    echo "   文件：$FILENAME"
    
    # 解析JSON并生成SQL
    # 临时SQL文件
    TEMP_SQL="/tmp/import_${BOOK_ID}.sql"
    
    # 生成INSERT语句
    cat > "$TEMP_SQL" << 'SQLHEAD'
SET NAMES utf8mb4;
START TRANSACTION;
SQLHEAD
    
    # 解析JSON并生成INSERT语句
    jq -r --arg book_id "$BOOK_ID" '
    to_entries | .[] | 
    .key as $idx | 
    .value | 
    @json "INSERT INTO dict_word (book_id, word, translation, phonetic_us, phonetic_uk, example_sentence, word_definition, sort_order, status, create_by, create_time) VALUES (\($book_id | @json), \(.name // .word // "" | @json), \(if .trans then (if (.trans | type) == "array" then (.trans | join("; ")) else .trans end) else "" end | @json), \(.usphone // "" | @json), \(.ukphone // "" | @json), \(.sentence // .example // "" | @json), \(.definition // .meaning // "" | @json), \($idx + 1), \"0\", \"system\", NOW());"
    ' "$JSON_FILE" >> "$TEMP_SQL" 2>/dev/null || {
        echo "   ❌ JSON解析失败"
        ((FAILED++))
        rm -f "$TEMP_SQL"
        continue
    }
    
    # 更新dict_book表的is_loaded状态
    TOTAL_COUNT=$(jq '. | length' "$JSON_FILE")
    cat >> "$TEMP_SQL" << SQLFOOT
UPDATE dict_book SET is_loaded = '1', length = $TOTAL_COUNT WHERE id = '$BOOK_ID';
COMMIT;
SQLFOOT
    
    # 执行SQL
    docker exec -i $DB_CONTAINER mysql -u$DB_USER -p$DB_PASS \
        --default-character-set=utf8mb4 $DB_NAME < "$TEMP_SQL" 2>/dev/null
    
    if [ $? -eq 0 ]; then
        echo "   ✅ 成功导入 $TOTAL_COUNT 个单词"
        ((SUCCESS++))
    else
        echo "   ❌ 导入失败"
        ((FAILED++))
    fi
    
    # 清理临时文件
    rm -f "$TEMP_SQL"
    
done <<< "$BOOKS"

echo ""
echo "[3/3] 导入完成！"
echo "=========================================="
echo "📊 导入统计："
echo "   ✅ 成功：$SUCCESS 个"
echo "   ⏭️  跳过：$SKIPPED 个"
echo "   ❌ 失败：$FAILED 个"
echo "=========================================="
echo ""

# 显示结果统计
echo "查看导入结果："
docker exec -i $DB_CONTAINER mysql -u$DB_USER -p$DB_PASS \
    --default-character-set=utf8mb4 $DB_NAME -e "
SELECT 
    b.id, 
    b.name, 
    b.is_loaded,
    b.length as recorded_count,
    COUNT(w.id) as actual_count
FROM dict_book b
LEFT JOIN dict_word w ON b.id = w.book_id
GROUP BY b.id, b.name, b.is_loaded, b.length
ORDER BY b.name;
" 2>/dev/null | grep -v "Warning"

echo ""
echo "✨ 完成！现在您的词典单词都已存储在数据库中了。"

